from openpyxl import load_workbook


class ParseExcelRobot(object):
    def __init__(self, xlsx_path, sheet_name=None):
        """
        :param xlsx_path:
        :param sheet_name:
        """
        self.work_excel = load_workbook(xlsx_path)
        if sheet_name is None:
            self.work_sheet = self.work_excel.active
        else:
            self.work_sheet = self.work_excel[sheet_name]
        self.xlsx_path = xlsx_path

    def save_data(self):
        """
        保存文件
        :return:
        """
        self.work_excel.save(self.xlsx_path)

    def close_xlsx(self):
        self.work_excel.close()

    @property
    def titles(self):
        return [c.value for c in self.work_sheet[1]]

    def get_col_num_by_col_name(self, col_name):
        """
        根据给定的字段名字，给出索引编号
        :param col_name:
        :return:
        """
        return self.titles.index(col_name)+1

    def choose_sheet(self, sheet_name):
        """
        选择切换sheet
        :param sheet_name:
        :return:
        """
        self.work_sheet = self.work_excel[sheet_name]

    def choose_col(self, col_name, whether_value=True):
        """
        根据列名，选择单列数据
        :param col_name:
        :param whether_value:
        :return:
        """
        col_index = self.titles.index(col_name)+1
        res = list(self.work_sheet.iter_cols(min_col=col_index, max_col=col_index, max_row=self.work_sheet.max_row, min_row=0, values_only=whether_value))[0]
        return res

    def choose_row(self, row_num, choose_cols, whether_value=True):
        """
        根据行号，选择单行数据
        :param row_num:
        :param choose_cols:
        :param whether_value:
        :return:
        """
        choose_r = list()
        for choose_col in choose_cols:
            choose_r.append(self.work_sheet.cell(row_num, choose_col))

        res = choose_r
        if whether_value:
            res = [c.value for c in choose_r]

        return res

    def get_test_records_to_run(self, whether_col, choose_cols):
        """
        从测试记录表中，获取需要的用例
        :param whether_col:
        :param choose_cols:
        :return:
        """
        whether_to_run = self.choose_col(whether_col)
        choose_col_num = [self.get_col_num_by_col_name(cc) for cc in choose_cols]

        to_run_test_records = list()
        for rn, rc in enumerate(whether_to_run):
            if rc is None:
                one_record = list()
                excel_row_num = rn+1    # openpyxl包中索引从1开始
                one_record.extend(self.choose_row(excel_row_num, choose_col_num))
                one_record.append(excel_row_num)    # 添加excel的行号，方便回写数据
                to_run_test_records.append(one_record)
        return to_run_test_records

    def write_cell_value(self, row_num, col_num, write_value, font_color=None):
        self.work_sheet.cell(row_num, col_num).value = write_value
